#!/usr/bin/env python
# coding=utf-8
# __author__ = 'Yunchao Ling'

import MySQLdb
from pymongo import MongoClient
import sys
from tqdm import tqdm


def InsertDescriptor(YEAR):
    # MongoDB Config
    DB_NAME = "mesh"
    COLLECTION_NAME = "desc" + YEAR
    connection = MongoClient('localhost', 27017)
    db = connection[DB_NAME]
    collection = db[COLLECTION_NAME]

    # MySQL config
    DATABASE_NAME = 'plosp'
    HOST = 'localhost'
    USER_NAME = 'plosp'
    PASSWORD = 'plosp'
    CHAR_SET = 'utf8'
    conn = MySQLdb.connect(host=HOST, user=USER_NAME, passwd=PASSWORD, db=DATABASE_NAME, charset=CHAR_SET)

    for record in tqdm(collection.find()):
        id = record["DescriptorID"]
        name = record["DescriptorName"]
        terms = record["TermList"]
        cursor = conn.cursor()
        sql = "insert into tb_dds_mesh_descriptor(id,name) values(%s,%s)"
        params = (id + "." + YEAR, name)
        cursor.execute(sql, params)
        count = 0
        for term in terms:
            if term != name:
                count += 1
                params2 = (id + "." + YEAR + "." + str(count), term)
                cursor.execute(sql, params2)
        conn.commit()
        cursor.close()
    conn.close()
    connection.close()


def InsertQualifier(YEAR):
    # MongoDB Config
    DB_NAME = "mesh"
    COLLECTION_NAME = "qual" + YEAR
    connection = MongoClient('localhost', 27017)
    db = connection[DB_NAME]
    collection = db[COLLECTION_NAME]

    # MySQL config
    DATABASE_NAME = 'plosp'
    HOST = 'localhost'
    USER_NAME = 'plosp'
    PASSWORD = 'plosp'
    CHAR_SET = 'utf8'
    conn = MySQLdb.connect(host=HOST, user=USER_NAME, passwd=PASSWORD, db=DATABASE_NAME, charset=CHAR_SET)

    for record in tqdm(collection.find()):
        id = record["QualifierID"]
        name = record["QualifierName"]
        terms = record["TermList"]
        cursor = conn.cursor()
        sql = "insert into tb_dds_mesh_qualifier(id,name) values(%s,%s)"
        params = (id + "." + YEAR, name)
        cursor.execute(sql, params)
        count = 0
        for term in terms:
            if term != name:
                count += 1
                params2 = (id + "." + YEAR + "." + str(count), term)
                cursor.execute(sql, params2)
        conn.commit()
        cursor.close()
    conn.close()
    connection.close()


def UpdateNumberTree(YEAR):
    # MongoDB Config
    DB_NAME = "mesh"
    COLLECTION_NAME = "desc" + YEAR
    connection = MongoClient('localhost', 27017)
    db = connection[DB_NAME]
    collection = db[COLLECTION_NAME]

    # MySQL config
    DATABASE_NAME = 'plosp'
    HOST = 'localhost'
    USER_NAME = 'plosp'
    PASSWORD = 'plosp'
    CHAR_SET = 'utf8'
    conn = MySQLdb.connect(host=HOST, user=USER_NAME, passwd=PASSWORD, db=DATABASE_NAME, charset=CHAR_SET)

    for record in tqdm(collection.find()):
        id = record["DescriptorID"]
        name = record["DescriptorName"]
        if record.has_key("TreeNumberList"):
            trees = record["TreeNumberList"]
            sql = "SELECT id FROM tb_dds_mesh_descriptor_tree WHERE descriptor_id=%s and tree=%s"
            for tree in trees:
                cursor = conn.cursor()
                params = (id, tree)
                cursor.execute(sql, params)
                results = cursor.fetchall()
                if len(results) == 0:
                    sql2 = "INSERT INTO tb_dds_mesh_descriptor_tree VALUES(0,%s,%s,%s,%s)"
                    tree_root = tree.split(".")[0]
                    params2 = (id, name, tree_root, tree)
                    cursor.execute(sql2, params2)
                else:
                    for result in results:
                        tree_id = str(result[0])
                        sql3 = "UPDATE tb_dds_mesh_descriptor_tree SET name = %s, tree_root = %s WHERE id = %s"
                        tree_root = tree.split(".")[0]
                        params3 = (name, tree_root, tree_id)
                        cursor.execute(sql3, params3)
                conn.commit()
                cursor.close()
    conn.close()
    connection.close()


if __name__ == "__main__":
    YEAR = sys.argv[1]
    InsertDescriptor(YEAR)
    InsertQualifier(YEAR)
    UpdateNumberTree(YEAR)
